Imports System.Data
Imports System.Data.SqlClient

Public Class MemberSignPackage

    Dim MemberID As Integer = 0
    Public Mode As String
    Dim ValidMemberNo As Boolean

    Private Sub MemberSignPackage_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        LoadIcon(Me)
        RefreshGrid()
        LoadDataToLookUpEdit("SELECT * FROM Category_People ORDER BY CategoryName", "CategoryID", "CategoryName", "Category", cboCategory)

        If Mode = "Single" Then
            'Me.XtraTabControl1.TabPages.Item(0).PageVisible = True
            'Me.XtraTabControl1.TabPages.Item(1).PageVisible = False
            'XtraTabControl1.SelectedTabPageIndex = 0
            Me.txtMemberNo.Focus()


        Else
            'Me.XtraTabControl1.TabPages.Item(0).PageVisible = False
            'Me.XtraTabControl1.TabPages.Item(1).PageVisible = True
            Me.txtFrom.Focus()
        End If


    End Sub

    Private Sub RefreshGrid()
        Dim str As String
        str = "SELECT * FROM Packages WHERE Status = 1"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim da As New SqlDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds, "Packages")
        Me.ImageListBoxControl1.DataSource = ds.Tables(0)
        Me.ImageListBoxControl1.ValueMember = "ID"
        Me.ImageListBoxControl1.DisplayMember = "PackageName"
        Me.ImageListBoxControl1.ImageIndexMember = 1


    End Sub

    Public Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()

        ' Add any initialization after the InitializeComponent() call.
        '  Me.txtMemberNo.Focus()

    End Sub

    Private Sub ImageListBoxControl1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ImageListBoxControl1.Click
        If Me.ImageListBoxControl1.ItemCount = 0 Then
            Exit Sub
        End If

        Dim selectedID As Integer = ImageListBoxControl1.SelectedValue
        ShowPackageDetail(selectedID)


    End Sub


    Private Sub ShowPackageDetail(ByVal ID As Integer)


        Dim str As String
        str = "SELECT * FROM Packages WHERE ID = " & ID
        Dim cmd As New SqlCommand(str, gCnnPCE)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            Me.lblPackageName.Text = dr.Item("PackageName").ToString
            Me.lblPrice.Text = dr.Item("RefPrice").ToString
            Me.lbl01.Text = dr.Item("Item01").ToString
            Me.lbl02.Text = dr.Item("Item02").ToString
            Me.lbl03.Text = dr.Item("Item03").ToString
            Me.lbl04.Text = dr.Item("Item04").ToString
            Me.lbl05.Text = dr.Item("Item05").ToString
            Me.lbl06.Text = dr.Item("Item06").ToString
            Me.lbl07.Text = dr.Item("Item07").ToString
            Me.lbl08.Text = dr.Item("Item08").ToString
            Me.lbl09.Text = dr.Item("Item09").ToString
            Me.lbl10.Text = dr.Item("Item10").ToString
            Me.lbl11.Text = dr.Item("Item11").ToString
            Me.lbl12.Text = dr.Item("Item12").ToString
            Me.lbl13.Text = dr.Item("Item13").ToString
            Me.lbl14.Text = dr.Item("Item14").ToString
            Me.lbl15.Text = dr.Item("Item15").ToString
            Me.lbl16.Text = dr.Item("Item16").ToString
            Me.lbl17.Text = dr.Item("Item17").ToString
            Me.lbl18.Text = dr.Item("Item18").ToString
            Me.lbl19.Text = dr.Item("Item19").ToString
            Me.lbl20.Text = dr.Item("Item20").ToString

        End While
        dr.Close()

        If MemberID <> 0 Then
            str = "SELECT COUNT(ID) FROM MemberPackage WHERE MemberID = @MemberID AND PackageID = @PackageID"
            cmd.CommandText = str
            cmd.Parameters.AddWithValue("@MemberID", MemberID)
            cmd.Parameters.AddWithValue("@PackageID", ID)
            If cmd.ExecuteScalar > 0 Then
                Me.btnSignThisPackage.Enabled = False
                Me.btnSignThisPackage.Text = "Already Signed"
            Else
                Me.btnSignThisPackage.Enabled = True
                Me.btnSignThisPackage.Text = "Sign This Package"
            End If
        End If



    End Sub

    Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
        Dim frm As New People
        frm.SearchMode = True
        frm.ShowDialog()

        Me.txtMemberNo.Text = FGT_MemberNo
        FGT_MemberNo = ""

        SearchMemberInfo(Me.txtMemberNo.Text)

    End Sub

    Private Sub SearchMemberInfo(ByVal MemberCode As String)
        Dim str As String
        str = "SELECT * FROM People WHERE CodeNo = @Code"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        cmd.Parameters.AddWithValue("@Code", MemberCode)
        Dim dr As SqlDataReader
        dr = cmd.ExecuteReader
        While dr.Read
            Me.lblMemberName.Text = dr.Item("Name").ToString
            MemberID = dr.Item("ID")
        End While
        dr.Close()

    End Sub

    Private Sub txtMemberNo_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtMemberNo.KeyDown
        If e.KeyCode = Keys.Enter Then
            If Me.txtMemberNo.Text <> "" Then
                SearchMemberInfo(Me.txtMemberNo.Text.Trim)
            End If
        End If
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSignThisPackage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSignThisPackage.Click
        Select Case Mode
            Case "Single"
                If MemberID = 0 Then
                    MsgBox("Please Choose Member", MsgBoxStyle.Exclamation)
                    Me.txtMemberNo.Focus()
                    Exit Sub
                End If

                If Me.ImageListBoxControl1.ItemCount = 0 Then
                    Exit Sub
                End If

                If MsgBox("Are You Sure Want to Sign This Package ?", MsgBoxStyle.YesNo) = MsgBoxResult.No Then
                    Exit Sub
                End If

                Dim str As String
                str = "INSERT INTO MemberPackage (MemberID, PackageID, SignedDate)VALUES(@MemberID, @PackageID, @SignedDate)"
                Dim cmd As New SqlCommand(str, gCnnPCE)
                cmd.Parameters.AddWithValue("@MemberID", MemberID)
                cmd.Parameters.AddWithValue("@PackageID", Me.ImageListBoxControl1.SelectedValue)
                cmd.Parameters.AddWithValue("@SignedDate", Now)
                If cmd.ExecuteNonQuery > 0 Then
                    MsgBox("Signed Successfully", MsgBoxStyle.Information)
                    Me.btnSignThisPackage.Enabled = False
                    Me.btnSignThisPackage.Text = "Already Signed"

                    'Insert Into Transaction



                Else
                    MsgBox("Fail to Sign", MsgBoxStyle.Exclamation)
                End If
            Case "Batch"

                If ValidMemberNo = False Then
                    Exit Sub
                End If

                If DxValidationProvider1.Validate = False Then
                    Exit Sub
                End If

                Dim str As String
                str = "INSERT INTO People (CodeNo, Name, ExpireDate, NeverExpireYesNo, Category) VALUES (@CodeNo, @Name, @ExpireDate, @NeverExpireYesNo, @Category)"
                Dim cmd As New SqlCommand(str, gCnnPCE)
                Dim ExpireDate As Date = New Date(2100, 12, 1)
                Dim i As Integer
                For i = Me.txtFrom.Text To Me.txtTo.Text
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@CodeNo", i)
                    cmd.Parameters.AddWithValue("@Name", Me.txtName.Text)
                    cmd.Parameters.AddWithValue("@ExpireDate", ExpireDate)
                    cmd.Parameters.AddWithValue("@NeverExpireYesNo", 1)
                    cmd.Parameters.AddWithValue("@Category", Me.cboCategory.EditValue)
                    cmd.ExecuteNonQuery()
                Next

                str = "INSERT INTO MemberPackage (MemberID, PackageID, SignedDate)VALUES(@MemberID, @PackageID, @SignedDate)"
                cmd.CommandText = str
                For i = Me.txtFrom.Text To Me.txtTo.Text
                    cmd.Parameters.Clear()
                    cmd.Parameters.AddWithValue("@MemberID", GetMemberIDFromMemberCode(i))
                    cmd.Parameters.AddWithValue("@PackageID", Me.ImageListBoxControl1.SelectedValue)
                    cmd.Parameters.AddWithValue("@SignedDate", Now)
                    cmd.ExecuteNonQuery()
                Next

                MsgBox("Signed Successfully", MsgBoxStyle.Information)

        End Select

    End Sub


    Private Function GetMemberIDFromMemberCode(ByVal Code As String) As Integer
        Dim str As String
        str = "SELECT ID From People WHERE CodeNo = @CodeNo"
        Dim cmd As New SqlCommand(str, gCnnPCE)
        cmd.Parameters.AddWithValue("@CodeNo", Code)
        Return cmd.ExecuteScalar
    End Function

    Private Sub txtFrom_EditValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFrom.EditValueChanged

        CheckValid()

    End Sub

    Private Sub CheckValid()
        If Me.txtFrom.Text = "" Or Me.txtTo.Text = "" Then
            ValidMemberNo = False
            Exit Sub
        End If

        If IsNumeric(Me.txtFrom.Text) = False Or IsNumeric(Me.txtTo.Text) = False Then
            Me.picFalse.Visible = True
            Me.picFalse.BringToFront()
            lblValid.Visible = True
            Me.lblValid.Text = "Not Valid Member No"
            ValidMemberNo = False
            Me.lblValid.BringToFront()
            Exit Sub
        End If

        If CInt(Me.txtTo.Text) < CInt(Me.txtFrom.Text) Then
            Me.picFalse.Visible = True
            Me.picFalse.BringToFront()
            lblValid.Visible = True
            Me.lblValid.Text = "Not Valid Member No"
            Me.lblValid.BringToFront()
            ValidMemberNo = False
            Exit Sub
        End If

        Me.picTrue.Visible = True
        Me.picTrue.BringToFront()
        lblValid.Visible = True
        Me.lblValid.Text = "Valid Member No"
        Me.lblValid.BringToFront()
        ValidMemberNo = True
    End Sub

    Private Sub txtTo_EditValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtTo.EditValueChanged
        CheckValid()
    End Sub
End Class